﻿using System;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace PKTools.Database
{
    partial class Sql
    {
        int ExecuteNonQuery(string query, params object[] objs)
        {
            Open();
            SetCommandText(query, objs);

            using (new Stoper("execute non-query", () => this.command.CommandText))
                return this.command.ExecuteNonQuery();
        }

        public int ExecuteSP(string procedure, bool forceTransaction = true)
        {
            Open();

            this.command.CommandType = CommandType.StoredProcedure;
            this.command.CommandText = procedure;

            bool
                internalTransaction = false;

            if (this.Transaction == null && forceTransaction)
            {
                BeginTransaction();
                internalTransaction = true;
            }

            try
            {
                int
                    result;

                using (new Stoper("execute SP", () => procedure))
                    result = this.command.ExecuteNonQuery();

                if (internalTransaction)
                    Commit();

                return result;
            }
            catch (Exception ex)
            {
                if (internalTransaction)
                    Rollback();

                Log.Entry(LogSeverity.Error, "execute SP failed: {0}", ex.Message);

                throw;
            }
        }

        string GetTableQuery<T>()
        {
            var
                type = typeof(T);

            return type.GetCustomAttribute<Table>().IsNull(e => e.Key, type.Name);
        }

        string GetFieldQuery()
        {
            string
                query = String.Empty;

            foreach (var e in this._objMap)
                query += "," + FormatColumn(e.Key);

            return query.Substring(1);
        }

        string GetKeyValueQuery<T>(Func<PropertyInfo, int, object> valueF)
        {
            string
                query = String.Empty;
            int
                i = 0;

            foreach (var e in this._objMap)
                if (e.Value.IsPK)
                {
                    AddInput(e.Value.PI.Name, valueF(e.Value.PI, i++));
                    query += " AND " + FormatColumn(e.Key) + " = @" + e.Value.PI.Name;
                }

            if (query == String.Empty)
                throw new Exception("object without PK: " + typeof(T).Name);

            return query.Substring(5);
        }

        public void Insert<T>(T element, bool identityInsert = true) where T : Spyed
        {
            if (element == null)
                return;

            Clear();

            GetObjectMap<T>();

            string
                fieldQuery = String.Empty,
                query = String.Empty;
            PropertyInfo
                identity = null;

            foreach (var e in this._objMap)
            {
                var
                    temp = e.Value.PI.GetCustomAttribute<Identity>();
                if (temp != null)
                    identity = e.Value.PI;

                if (temp == null || !identityInsert)
                {
                    AddInput(e.Value.PI.Name, e.Value.PI.GetValue(element));

                    fieldQuery += "," + FormatColumn(e.Key);
                    query += ",@" + e.Value.PI.Name;
                }
            }

            if (fieldQuery == String.Empty)
                throw new Exception("no fields to insert");

            ExecuteNonQuery("INSERT {0} ({1}) VALUES ({2})", 
                GetTableQuery<T>(), 
                fieldQuery.Substring(1),
                query.Substring(1));

            if (identityInsert && identity != null)
                identity.SetValue(element, Convert.ChangeType(this.GetIdentity(), identity.PropertyType));

            this._toResetElements.Add(element);
        }

        public int Delete<T>(T element)
        {
            if (element == null)
                return -1;

            Clear();

            GetObjectMap<T>();

            return ExecuteNonQuery("DELETE {0} WHERE {1}",
                GetTableQuery<T>(),
                GetKeyValueQuery<T>((e, i) => e.GetValue(element)));
        }

        public int DeleteBy<T>(Expression<Func<T, bool>> expression)
        {
            Clear();

            GetObjectMap<T>();

            return this.ExecuteNonQuery("DELETE {0} WHERE {1}", 
                GetTableQuery<T>(),
                SqlExpressionVisitor.Query(
                    this._objMap.ToDictionary(e => e.Value.PI.Name, e => e.Key),
                    expression));
        }

        public int DeleteAll<T>()
        {
            Clear();

            GetObjectMap<T>();

            return this.ExecuteNonQuery("DELETE {0}", GetTableQuery<T>());
        }

        public int Update<T>(T element) where T : Spyed
        {
            if (element == null)
                return -1;

            Clear();

            GetObjectMap<T>();

            string
                query = String.Empty;

            foreach (var key in element.__GetChanged())                
            {
                var
                    e = this._objMap[key];

                if (e.PI.GetCustomAttribute<PK>() != null)
                    continue;

                AddInput(e.PI.Name, e.PI.GetValue(element));
                query += "," + FormatColumn(key) + " = @" + e.PI.Name;
            }

            if (query == String.Empty)
            {
                Log.Entry(LogSeverity.Debug, "no changes for object");
                return -1; // nothing changed
            }

            this._toResetElements.Add(element);

            return ExecuteNonQuery("UPDATE {0} SET {1} WHERE {2}",
                GetTableQuery<T>(),
                query.Substring(1),
                GetKeyValueQuery<T>((e, i) => e.GetValue(element)));
        }
    }
}
